from flask import Blueprint, request, jsonify
from app import db
from app.models.datasource import DataSource
from app.services.datasource_service import get_table_schema
import pymysql
from app.services.fake_data_service import generate_fake_batch
import random
import string
from datetime import datetime, timedelta
from faker import Faker
import uuid

fake_data_bp = Blueprint('fake_data', __name__)

# 初始化Faker
fake = Faker('zh_CN')

@fake_data_bp.route('/schema', methods=['GET'])
def get_table_schema_api():
    """获取数据库表结构"""
    datasource_id = request.args.get('datasourceId')
    table_name = request.args.get('tableName')
    
    if not datasource_id or not table_name:
        return jsonify({
            'code': 400,
            'message': '缺少必填参数: datasourceId 或 tableName'
        })
    
    try:
        datasource = DataSource.query.get(datasource_id)
        if not datasource:
            return jsonify({
                'code': 404,
                'message': '数据源不存在'
            })
        
        # 获取表结构
        schema = get_table_schema(
            datasource.host,
            datasource.port,
            datasource.username,
            datasource.password,
            datasource.database,
            table_name
        )
        
        # 转换为假数据生成器需要的格式
        fields = []
        for field in schema:
            field_config = {
                'name': field['name'],
                'type': map_db_type_to_fake_type(field['type']),
                'db_type': field['type'],
                'nullable': field.get('nullable', True),
                'default': field.get('default'),
                'comment': field.get('comment', ''),
                'is_primary': field.get('is_primary', False),
                'auto_increment': 'auto_increment' in field.get('extra', '')
            }
            
            # 根据数据库字段类型设置默认配置
            set_field_default_config(field_config)
            fields.append(field_config)
        
        return jsonify({
            'code': 200,
            'data': {
                'table_name': table_name,
                'fields': fields
            }
        })
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': f'获取表结构失败: {str(e)}'
        })

@fake_data_bp.route('/generate', methods=['POST'])
def generate_fake_data():
    """生成假数据并插入到数据库"""
    data = request.json
    
    # 验证必填字段
    required_fields = ['datasource_id', 'table_name', 'record_count', 'fields']
    for field in required_fields:
        if field not in data:
            return jsonify({
                'code': 400,
                'message': f'缺少必填字段: {field}'
            })
    
    datasource_id = data['datasource_id']
    table_name = data['table_name']
    record_count = data['record_count']
    fields = data['fields']
    truncate_table = data.get('truncate_table', False)
    
    try:
        # 获取数据源
        datasource = DataSource.query.get(datasource_id)
        if not datasource:
            return jsonify({
                'code': 404,
                'message': '数据源不存在'
            })
        
        # 生成假数据
        fake_data = []
        for i in range(record_count):
            row = {}
            for field in fields:
                if field.get('generate', True):
                    row[field['name']] = generate_field_data(field, i)
            fake_data.append(row)
        
        # 生成插入SQL
        insert_sql = generate_insert_sql(table_name, fake_data, datasource.type)
        
        # 执行SQL到数据源
        try:
            execute_data_to_datasource(datasource, table_name, fake_data, truncate_table)
        except Exception as e:
            return jsonify({
                'code': 500,
                'message': f'插入数据到数据源失败: {str(e)}'
            })
        
        # 返回结果（只返回前10条作为预览）
        preview_data = fake_data[:10] if len(fake_data) > 10 else fake_data
        
        return jsonify({
            'code': 200,
            'message': '数据生成成功',
            'data': {
                'table_name': table_name,
                'record_count': record_count,
                'field_count': len([f for f in fields if f.get('generate', True)]),
                'datasource_name': datasource.name,
                'preview_data': preview_data
            }
        })
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': f'生成数据失败: {str(e)}'
        })

@fake_data_bp.route('/preview', methods=['POST'])
def preview_fake_data():
    """预览假数据"""
    data = request.json
    
    fields = data.get('fields', [])
    preview_count = min(data.get('record_count', 10), 10)  # 最多预览10条
    
    try:
        # 生成预览数据
        preview_data = []
        for i in range(preview_count):
            row = {}
            for field in fields:
                row[field['name']] = generate_field_data(field, i)
            preview_data.append(row)
        
        return jsonify({
            'code': 200,
            'data': preview_data
        })
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': f'预览数据失败: {str(e)}'
        })

def generate_field_data(field, index=0):
    """根据字段配置生成数据"""
    field_type = field['type']
    field_name = field['name']
    db_type = field.get('db_type', '')
    
    # 获取字段的最大长度限制
    max_db_length = extract_field_length(db_type)
    
    if field_type == 'id':
        return index + 1
    elif field_type == 'integer':
        min_val = field.get('min', 0)
        max_val = field.get('max', 1000)
        return random.randint(min_val, max_val)
    elif field_type == 'float':
        min_val = field.get('min', 0.0)
        max_val = field.get('max', 1000.0)
        precision = field.get('precision', 2)
        return round(random.uniform(min_val, max_val), precision)
    elif field_type == 'string':
        min_length = field.get('min_length', 1)
        max_length = field.get('max_length', 10)
        
        # 确保不超过数据库字段长度限制
        if max_db_length:
            max_length = min(max_length, max_db_length)
            min_length = min(min_length, max_length)
        
        length = random.randint(min_length, max_length)
        result = ''.join(random.choices(string.ascii_letters + string.digits, k=length))
        
        # 再次确保长度不超限
        if max_db_length and len(result) > max_db_length:
            result = result[:max_db_length]
        
        return result
    elif field_type == 'datetime':
        start_date = field.get('start_date', '2023-01-01 00:00:00')
        end_date = field.get('end_date', '2023-12-31 23:59:59')
        
        start_dt = datetime.strptime(start_date, '%Y-%m-%d %H:%M:%S')
        end_dt = datetime.strptime(end_date, '%Y-%m-%d %H:%M:%S')
        
        # 生成随机时间
        time_between = end_dt - start_dt
        days_between = time_between.days
        if days_between > 0:
            random_days = random.randrange(days_between)
            random_seconds = random.randrange(24 * 60 * 60)
            random_dt = start_dt + timedelta(days=random_days, seconds=random_seconds)
        else:
            random_dt = start_dt
        
        return random_dt.strftime('%Y-%m-%d %H:%M:%S')
    elif field_type == 'date':
        start_date = field.get('start_date', '2023-01-01')
        end_date = field.get('end_date', '2024-12-31')
        
        start_dt = datetime.strptime(start_date, '%Y-%m-%d')
        end_dt = datetime.strptime(end_date, '%Y-%m-%d')
        
        # 生成随机日期
        time_between = end_dt - start_dt
        days_between = time_between.days
        if days_between > 0:
            random_days = random.randrange(days_between)
            random_dt = start_dt + timedelta(days=random_days)
        else:
            random_dt = start_dt
        
        return random_dt.strftime('%Y-%m-%d')
    elif field_type == 'boolean':
        return random.choice([True, False])
    elif field_type == 'enum':
        values = field.get('values', '').split(',')
        values = [v.strip() for v in values if v.strip()]
        if values:
            selected = random.choice(values)
            # 确保枚举值不超过字段长度限制
            if max_db_length and len(selected) > max_db_length:
                selected = selected[:max_db_length]
            return selected
        return ''
    elif field_type == 'name':
        result = fake.name()
        # 确保姓名长度不超限
        if max_db_length and len(result) > max_db_length:
            result = result[:max_db_length]
        return result
    elif field_type == 'phone':
        result = fake.phone_number()
        # 确保手机号长度不超限
        if max_db_length and len(result) > max_db_length:
            result = result[:max_db_length]
        return result
    elif field_type == 'email':
        result = fake.email()
        # 确保邮箱长度不超限
        if max_db_length and len(result) > max_db_length:
            # 如果邮箱太长，生成一个简短的
            result = f"user{random.randint(1, 9999)}@test.com"
            if len(result) > max_db_length:
                result = result[:max_db_length]
        return result
    elif field_type == 'address':
        result = fake.address().replace('\n', ' ')
        # 确保地址长度不超限
        if max_db_length and len(result) > max_db_length:
            result = result[:max_db_length]
        return result
    elif field_type == 'ip':
        return fake.ipv4()
    elif field_type == 'uuid':
        result = str(uuid.uuid4())
        # UUID通常是36个字符，如果字段太短就截断
        if max_db_length and len(result) > max_db_length:
            result = result[:max_db_length]
        return result
    elif field_type == 'text':
        # 文本类型，生成较长的文本
        min_length = field.get('min_length', 10)
        max_length = field.get('max_length', 100)
        
        # 确保不超过数据库字段长度限制
        if max_db_length:
            max_length = min(max_length, max_db_length)
            min_length = min(min_length, max_length)
        
        length = random.randint(min_length, max_length)
        result = fake.text(max_nb_chars=length)
        
        # 再次确保长度不超限
        if max_db_length and len(result) > max_db_length:
            result = result[:max_db_length]
        
        return result
    else:
        return ''

def generate_create_table_sql(table_name, fields, datasource_type='mysql'):
    """生成建表SQL"""
    if datasource_type == 'mysql':
        sql = f"CREATE TABLE `{table_name}` (\n"
        field_sqls = []
        
        for field in fields:
            field_sql = f"  `{field['name']}` {get_mysql_field_type(field)}"
            
            # 主键
            if field['type'] == 'id':
                field_sql += " NOT NULL AUTO_INCREMENT"
            else:
                field_sql += " NULL"
            
            field_sqls.append(field_sql)
        
        # 添加主键约束
        has_id = any(field['type'] == 'id' for field in fields)
        if has_id:
            id_field = next(field for field in fields if field['type'] == 'id')
            field_sqls.append(f"  PRIMARY KEY (`{id_field['name']}`)")
        
        sql += ",\n".join(field_sqls)
        sql += "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
        
        return sql
    
    # 其他数据库类型可以在这里扩展
    return ""

def get_mysql_field_type(field):
    """获取MySQL字段类型"""
    field_type = field['type']
    
    if field_type == 'id':
        return 'INT(11)'
    elif field_type == 'integer':
        return 'INT(11)'
    elif field_type == 'float':
        precision = field.get('precision', 2)
        return f'DECIMAL(10,{precision})'
    elif field_type == 'string':
        max_length = field.get('max_length', 255)
        if max_length <= 255:
            return f'VARCHAR({max_length})'
        else:
            return 'TEXT'
    elif field_type == 'datetime':
        return 'DATETIME'
    elif field_type == 'boolean':
        return 'TINYINT(1)'
    elif field_type == 'enum':
        values = field.get('values', '').split(',')
        values = [f"'{v.strip()}'" for v in values if v.strip()]
        if values:
            return f"ENUM({','.join(values)})"
        else:
            return 'VARCHAR(50)'
    elif field_type in ['name', 'phone', 'email', 'address']:
        return 'VARCHAR(255)'
    elif field_type == 'ip':
        return 'VARCHAR(45)'
    elif field_type == 'uuid':
        return 'VARCHAR(36)'
    else:
        return 'VARCHAR(255)'

def generate_insert_sql(table_name, data, datasource_type='mysql'):
    """生成插入SQL"""
    if not data:
        return ""
    
    if datasource_type == 'mysql':
        # 获取字段名
        fields = list(data[0].keys())
        field_names = ", ".join([f"`{field}`" for field in fields])
        
        # 生成VALUES部分
        values_list = []
        for row in data:
            values = []
            for field in fields:
                value = row[field]
                if value is None:
                    values.append('NULL')
                elif isinstance(value, bool):
                    values.append('1' if value else '0')
                elif isinstance(value, (int, float)):
                    values.append(str(value))
                else:
                    # 转义单引号
                    escaped_value = str(value).replace("'", "''")
                    values.append(f"'{escaped_value}'")
            
            values_list.append(f"({', '.join(values)})")
        
        # 分批插入，每批1000条
        batch_size = 1000
        sql_statements = []
        
        for i in range(0, len(values_list), batch_size):
            batch_values = values_list[i:i + batch_size]
            sql = f"INSERT INTO `{table_name}` ({field_names}) VALUES\n"
            sql += ",\n".join(batch_values) + ";"
            sql_statements.append(sql)
        
        return "\n\n".join(sql_statements)
    
    return ""

def map_db_type_to_fake_type(db_type):
    """将数据库字段类型映射为假数据类型"""
    db_type = db_type.lower()
    
    # 整数类型
    if 'int' in db_type or 'integer' in db_type:
        return 'integer'
    # 浮点数类型
    elif any(t in db_type for t in ['float', 'double', 'decimal', 'numeric']):
        return 'float'
    # 日期时间类型
    elif 'datetime' in db_type or 'timestamp' in db_type:
        return 'datetime'
    # 日期类型
    elif 'date' in db_type:
        return 'date'
    # 时间类型
    elif 'time' in db_type:
        return 'time'
    # 布尔类型
    elif 'bool' in db_type or 'tinyint(1)' in db_type:
        return 'boolean'
    # 枚举类型
    elif 'enum' in db_type:
        return 'enum'
    # 文本类型
    elif any(t in db_type for t in ['text', 'longtext', 'mediumtext']):
        return 'text'
    # 字符串类型
    elif any(t in db_type for t in ['char', 'varchar']):
        return 'string'
    # JSON类型
    elif 'json' in db_type:
        return 'json'
    # 默认为字符串
    else:
        return 'string'

def set_field_default_config(field_config):
    """根据字段名和类型设置默认配置"""
    field_name = field_config['name'].lower()
    field_type = field_config['type']
    db_type = field_config.get('db_type', '').lower()
    
    # 从数据库类型中提取长度信息
    db_length = extract_field_length(field_config.get('db_type', ''))
    
    # 根据字段名推断数据类型
    if 'id' in field_name and field_config['is_primary']:
        field_config['type'] = 'id'
        field_config['generate'] = False  # 主键ID通常不生成
    elif any(name in field_name for name in ['name', '姓名', 'username', 'user_name']):
        field_config['type'] = 'name'
    elif any(name in field_name for name in ['phone', 'mobile', '手机', '电话']):
        field_config['type'] = 'phone'
    elif any(name in field_name for name in ['email', '邮箱', 'mail']):
        field_config['type'] = 'email'
    elif any(name in field_name for name in ['address', '地址', 'addr']):
        field_config['type'] = 'address'
    elif any(name in field_name for name in ['ip', 'ip_address']):
        field_config['type'] = 'ip'
    elif any(name in field_name for name in ['uuid', 'guid']):
        field_config['type'] = 'uuid'
    elif any(name in field_name for name in ['age', '年龄']):
        field_config['type'] = 'integer'
        field_config['min'] = 1
        field_config['max'] = 100
    elif any(name in field_name for name in ['price', 'amount', 'money', '价格', '金额']):
        field_config['type'] = 'float'
        field_config['min'] = 0.01
        field_config['max'] = 10000.00
        field_config['precision'] = 2
    elif any(name in field_name for name in ['status', '状态']) and db_length and db_length <= 5:
        # 状态字段通常是短字符串或数字
        if db_length == 1:
            field_config['type'] = 'enum'
            field_config['values'] = '0,1'  # 常见的状态值
        else:
            field_config['type'] = 'enum'
            field_config['values'] = 'active,inactive,pending'
    
    # 根据字段类型设置默认值
    if field_type == 'integer':
        field_config.setdefault('min', 1)
        field_config.setdefault('max', 1000)
    elif field_type == 'float':
        field_config.setdefault('min', 0.0)
        field_config.setdefault('max', 1000.0)
        field_config.setdefault('precision', 2)
    elif field_type == 'string':
        # 根据数据库字段长度设置合理的字符串长度
        if db_length:
            max_length = min(db_length, 50)  # 最大不超过50个字符
            min_length = min(1, max_length)
        else:
            max_length = 20
            min_length = 1
        
        field_config.setdefault('min_length', min_length)
        field_config.setdefault('max_length', max_length)
    elif field_type == 'datetime':
        field_config.setdefault('start_date', '2023-01-01 00:00:00')
        field_config.setdefault('end_date', '2024-12-31 23:59:59')
    elif field_type == 'date':
        field_config.setdefault('start_date', '2023-01-01')
        field_config.setdefault('end_date', '2024-12-31')
    elif field_type == 'enum':
        # 尝试从数据库类型中提取枚举值
        db_type_orig = field_config.get('db_type', '')
        if 'enum' in db_type_orig.lower():
            try:
                enum_part = db_type_orig.split('(')[1].split(')')[0]
                values = [v.strip("'\"") for v in enum_part.split(',')]
                field_config['values'] = ','.join(values)
            except:
                field_config['values'] = '0,1,2'
        else:
            # 如果不是枚举类型但被设置为枚举，根据字段长度生成合适的值
            if db_length == 1:
                field_config['values'] = '0,1'
            elif db_length <= 5:
                field_config['values'] = 'Y,N'
            else:
                field_config['values'] = 'active,inactive,pending'
    
    # 默认生成数据
    field_config.setdefault('generate', True)

def extract_field_length(db_type):
    """从数据库字段类型中提取长度信息"""
    import re
    
    if not db_type:
        return None
    
    # 匹配类似 varchar(255), char(10), int(11) 等格式
    match = re.search(r'\((\d+)\)', db_type)
    if match:
        return int(match.group(1))
    
    # 对于一些固定长度的类型
    type_lower = db_type.lower()
    if type_lower == 'tinyint':
        return 4  # -128 to 127
    elif type_lower == 'smallint':
        return 6  # -32768 to 32767
    elif type_lower == 'mediumint':
        return 9  # -8388608 to 8388607
    elif type_lower == 'int' or type_lower == 'integer':
        return 11  # -2147483648 to 2147483647
    elif type_lower == 'bigint':
        return 20  # -9223372036854775808 to 9223372036854775807
    
    return None

def execute_data_to_datasource(datasource, table_name, data, truncate_table=False):
    """执行数据插入到数据源"""
    connection = pymysql.connect(
        host=datasource.host,
        port=int(datasource.port),
        user=datasource.username,
        password=datasource.password,
        database=datasource.database
    )
    
    cursor = connection.cursor()
    
    try:
        # 如果需要清空表
        if truncate_table:
            truncate_sql = f"TRUNCATE TABLE `{table_name}`"
            cursor.execute(truncate_sql)
        
        # 批量插入数据
        if data:
            # 获取字段名
            fields = list(data[0].keys())
            field_names = ", ".join([f"`{field}`" for field in fields])
            
            # 分批插入，每批1000条
            batch_size = 1000
            for i in range(0, len(data), batch_size):
                batch_data = data[i:i + batch_size]
                
                # 生成VALUES部分
                values_list = []
                for row in batch_data:
                    values = []
                    for field in fields:
                        value = row.get(field)
                        if value is None:
                            values.append('NULL')
                        elif isinstance(value, bool):
                            values.append('1' if value else '0')
                        elif isinstance(value, (int, float)):
                            values.append(str(value))
                        else:
                            # 转义单引号
                            escaped_value = str(value).replace("'", "''")
                            values.append(f"'{escaped_value}'")
                    
                    values_list.append(f"({', '.join(values)})")
                
                # 执行插入
                insert_sql = f"INSERT INTO `{table_name}` ({field_names}) VALUES {', '.join(values_list)}"
                cursor.execute(insert_sql)
        
        connection.commit()
        
    finally:
        cursor.close()
        connection.close()